<?php
/**
 * Description of ExamSet
 *
 * @author Chisheng Lu, Jeffy Shih
 * @copyright Copyright(c)2010 DoRISE team, Science Education center, NTNU.
 * @since 2010-06-23
 * @version 1.0
 */
class ExamSet {

    private $mysqli;

    function __construct() {
        //引入資料庫連結參數設定
        include '../inc/oaConn.inc.php';
        $this->mysqli = @new mysqli($mysqlHost,$mysqlUser,$mysqlPassword,$mysqlDB);
        if(mysqli_connect_errno()) {
            throw new Exception('無法建立資料庫連線！'.mysqli_connect_error());
            $this->mysqli = false;
            exit();
        }

        //連線使用編碼
        $this->mysqli->query("SET NAMES 'utf8'");
    }

    /**
     *
     * @param Array $aryExamSet
     * @return Integer return 0 is fail
     */
    public function addExamSet($aryExamSeT) {
        $intRtn = 0;
        $strNow = $this->getNowTime();
        $sql_addES = 'INSERT INTO `ExamSet` (';
        $sql_values = ' VALUES(';
        if (array_key_exists('EBId', $aryExamSeT)) {
            $sql_addES .= '`EBId`, ';
            $sql_values .= '\'' . $aryExamSet['EBId'] . '\', ';
        }
        if (array_key_exists('ESDuration', $aryExamSeT)) {
            $sql_addES .= '`ESDuration`, ';
            $sql_values .= $aryExamSet['ESDuration'] . ', ';
        }
        if (array_key_exists('ESDateBegin', $aryExamSeT)) {
            $sql_addES .= '`ESDateBegin`, ';
            $sql_values .= '\'' . $aryExamSeT['ESDateBegin'] . '\', ';
        }
        if (array_key_exists('ESDateEnd', $aryExamSeT)) {
            $sql_addES .= '`ESDateEnd`, ';
            $sql_values .= '\'' . $aryExamSeT['ESDateEnd'] . '\', ';
        }
        if (array_key_exists('ESHashCode', $aryExamSeT)) {
            $sql_addES .= '`ESHashCode`, ';
            $sql_values .= '\'' . $aryExamSeT['ESHashCode'] . '\', ';
        }
        if (array_key_exists('UserId', $aryExamSeT)) {
            $sql_addES .= '`UserId`, ';
            $sql_values .= '\'' . $aryExamSeT['UserId'] . '\', ';
        }
        $sql_addES .= '`ESCreated`, ';
        $sql_values .= '\'' . $strNow . '\' ';
        $sql_addES .= ')' . $sql_values . ')';

        $rs_addES = $this->mysqli->query($sql_addES);
        if (!$rs_addES) {
            throw new Exception('新增試卷基本資料時發生錯誤！');
        }else {
            $intRtn = $this->mysqli->insert_id;
        }
        return $intRtn;

    }

    /**
     *
     * @param Integer $intESId
     * @param Array $aryExamSet
     * @return Boolean
     */
    public function editExamSet($intESId, $aryExamSet) {
        //檢查施測編號是否為空白
        if (empty ($intESId)) {
            throw new Exception('缺少施測編號！');
        }
        $bolRtn = false;
        $strNow = $this->getNowTime();
        $sql_editES = 'UPDATE `ExamSet` SET ';
        if (array_key_exists('EBId', $aryExamSet)) {
            $sql_editES .= '`EBId` = ' . $aryExamSet['EBId'] . ', ';
        }
        if (array_key_exists('ESDuration', $aryExamSet)) {
            $sql_editES .= '`ESDuration` = ' . $aryExamSet['ESDuration'] . ', ';
        }
        if (array_key_exists('ESDateBegin', $aryExamSet)) {
            $sql_editES .= '`ESDateBegin` = \'' . $aryExamSet['ESDateBegin'] . '\', ';
        }
        if (array_key_exists('ESDateEnd', $aryExamSet)) {
            $sql_editES .= '`ESDateEnd` = \'' . $aryExamSet['ESDateEnd'] . '\', ';
        }
        if (array_key_exists('ESHashCode', $aryExamSet)) {
            $sql_editES .= '`ESHashCode` = \'' . $aryExamSet['ESHashCode'] . '\', ';
        }

        $sql_editES .= '`ESModified` = \'' . $strNow . '\' ';
        $sql_editES .= 'WHERE `ESId` = ' . $intESId . ';';

        $rs_editES = $this->mysqli->query($sql_editES);
        if (!$rs_editES) {
            throw new Exception('更新施測資料時發生錯誤！');
        }else {
            $bolRtn = true;
        }
        return $bolRtn;
    }

    /**
     *
     * @param Integer $intESId
     * @return Boolean
     */
    public function delExamSet($intESId) {
        $bolRtn = false;
        //檢查施測編號是否為空白
        if (empty ($intESId)) {
            throw new Exception('錯誤！缺少施測編號。');
        }

        //檢查 TestRecord 鐘是否已經有作答，有作答時不可以刪除
        $sql_chkTR = 'SELECT COUNT(*) FROM `TestRecord` WHERE `ESId`=' . $intESId;
        $rs_chkTR = $this->mysqli->query($sql_chkTR);
        $row_chkTR = $rs_chkTR->fetch_row();
        if($row_chkTR[0]>0) {
            throw new Exception('此施測已有作答內容，不允許刪除！');
        }else {
            //刪除
            $sql_delES = 'DELETE FROM `ExamSet` WHERE `ESId`=' . $intESId;
            $rs_delES = $this->mysqli->query($sql_delES);
            if (!$rs_delES) {
                throw new Exception('錯誤！刪除施測資料時發生錯誤。');
            }else {
                $bolRtn = true;
            }
        }

        return $bolRtn;
    }

    /**
     *
     * @param Integer $intESHashCode
     * @return Array
     */
    public function getExamSetInfoByESHashCode($intESHashCode) {
        //檢查施測HashCode是否為空白
        if (empty ($intESHashCode)) {
            throw new Exception('缺少施測HashCode！');
        }

        $sql_getESByESHashCode = 'SELECT * FROM `ExamSet` WHERE `ESHashCode`=' . $intESHashCode;
        $rs_getESByESHashCode = $this->mysqli->query($sql_getESByESHashCode);

        if (!$rs_getESByESHashCode) {
            throw new Exception('無法查詢該施測HashCode的資料！');
        }else {
            if ($rs_getESByESHashCode->num_rows == 0) {
                throw new Exception('無此施測HashCode的資料！');
            }else {
                $row_getESByESHashCode = $rs_getESByESHashCode->fetch_row();
                return $row_getESByESHashCode;
            }
        } //End If
    }

    /**
     *
     * @return Array
     */
    public function getExamSetcList() {
        $sql_getESList = 'SELECT * FROM `ExamSet`';
        $rs_getESList = $this->mysqli->query($sql_getESList);

        if (!$rs_getESList) {
            throw new Exception('無法查詢該施測資料的資料！');
        }else {

            $aryESList = array();
            while ($aryOneES = $rs_getESList->fetch_row()) {
                array_push($aryESList, $aryOneES);
            }
            return $aryESList;
        } //End If

    }

    /**
     *
     * @param Integer $intUserId
     * @return Array
     */
    public function getExamSetListByUserId($intUserId) {
        $sql_getESList = 'SELECT * FROM `ExamSet` WHERE `UserId`=' . $intUserId;
        $rs_getESList = $this->mysqli->query($sql_getESList);

        if (!$rs_getESList) {
            throw new Exception('無法查詢該施測資料的資料！');
        }else {

            $aryESList = array();
            while ($aryOneES = $rs_getESList->fetch_row()) {
                array_push($aryESList, $aryOneES);
            }
            return $aryESList;
        } //End If

    }

    /**
     *
     * @param Array $arySearchCondition
     * @return Array
     */
    public function getExamBasicSearchResult($arySearchCondition){
        //SQL Condition combination
        $sql_condition = '(`EBGradeBegin`>=' . $arySearchCondition['GradeBegin'] . ' AND `EBGradeEnd`<=' . $arySearchCondition['GradeEnd'] . ')';

        if(array_key_exists('SubjectId', $arySearchCondition)) {
            $sql_condition .= ' AND `EBSubjectId`=' . $arySearchCondition['SubjectId'];
        }

        if(array_key_exists('ReAnswer', $arySearchCondition)) {
            $sql_condition .= ' AND `EBReAnswer`=' . $arySearchCondition['ReAnswer'];
        }

        if(array_key_exists('SchKeyword', $arySearchCondition)) {
            $strSchKeyword = $arySearchCondition['SchKeyword'];
            $sql_condition .= ' AND `EBTitle` LIKE \'%' . $strSchKeyword . '%\'';
        }

        $sql_ExamSearch = 'SELECT * FROM `ExamBasic` WHERE ' . $sql_condition;
        $rs_ExamSearch = $this->mysqli->query($sql_ExamSearch);

        if(!$rs_ExamSearch){
            throw new Exception('搜尋試卷工具時發生錯誤！');
        }else{
            $arySearchResult = array();
            while($row_ExamSearch = $rs_ExamSearch->fetch_row()){
                array_push($arySearchResult, $row_ExamSearch);
            }

            return $arySearchResult;
        }

    }

    /**
     *
     * @return String
     */
    private function getNowTime() {
        return date('Y-m-d H:i:s');
    }

    function  __destruct() {
        if ($this->mysqli) {
            $this->mysqli->close();
            $this->mysqli = FALSE;
        }
    }
}

?>
